package com.scalar.chiptrack.adaptor.aati;

import java.io.File;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Types;
import java.util.ArrayList;
import com.scalar.ScalarException;
import com.scalar.chiptrack.adaptor.AdaptorConstants;
import com.scalar.chiptrack.adaptor.AdaptorUtils;
import com.scalar.chiptrack.adaptor.beceem.SalesParser;
import com.scalar.chiptrack.adaptor.utils.Parser;
import com.scalar.chiptrack.adaptor.utils.ParserFactory;
import com.scalar.chiptrack.adaptor.utils.ParserResult;
import com.scalar.chiptrack.adaptor.utils.XLSParserImpl;
import com.scalar.chiptrack.db.DBTransaction;
import com.scalar.chiptrack.db.DBXException;
import com.scalar.chiptrack.db.DataSource;
import com.scalar.chiptrack.sales.parser.SalesParserLineItem;
import com.scalar.chiptrack.utils.Logger;
import com.scalar.chiptrack.utils.StringUtils;

public class AATIProductLoader 
{
    private Logger m_parserLogger = null;

    public AATIProductLoader() throws Exception {
        try {
            m_parserLogger = new Logger("./logs", "Product Porting", "Parser", "debug", 1024 * 1000, 5 );
            AdaptorUtils.setLogger(m_parserLogger);
        }
        catch (Exception e) {
            e.printStackTrace();
        }
    }

    // To be implemented by sub classes
    public void execute() throws Exception 
    {
        boolean success = false;
        AdaptorUtils.printHeader("Products PORTING", "PARSER");
        File productFile = getFileForPorting(System.getProperty("productFile"));
        log("Parsing File:" + productFile.getName());
        System.out.println("Parsing Product Order File:" + productFile.getName());

        log("Parsing Product sheet....");
        System.out.println("Parsing Product sheet....");

        ArrayList<ProductStructure> productList = parseProductXLSFile( productFile );

        log("Parsing Product completed...");
        System.out.println("Parsing Product completed...");
        log("Parsing completed successfully...");
        System.out.println("Parsing completed successfully.....");
        log("Porting Product info...");
        System.out.println("Porting Product info...");

        DBTransaction dbTransaction = null;

        try {
            dbTransaction = DBTransaction.getInstance();
            dbTransaction.begin();
            saveProductInfo(dbTransaction, productList);
            if (dbTransaction != null) {
                dbTransaction.commit();
            }
            success = true;
         }
        catch (Exception e) {
            log("Exception while porting Sales Orders");
            m_parserLogger.logDebug(e.getMessage(), e);
            throw e;
        }
        finally {
            if (dbTransaction != null) {
                if (!success) {
                    try {
                        dbTransaction.rollback();
                    } catch (DBXException ignore) {
                    }
                }
                dbTransaction = null;
            }
        }
        System.out.println("********SEE LOG FILE FOR NOT PORTED LINE ITEMS********");
        log("Porting Products completed successfully....");
        System.out.println("Porting Products completed successfully....");
    }

    private void saveProductInfo(DBTransaction dbTransaction, ArrayList<ProductStructure> productList) throws ScalarException
    {
        Connection conn = null;
        CallableStatement cstmt = null;
        boolean success = false;
        int productListSize = (productList == null) ? 0 : productList.size();
        ProductStructure product = null;

        try 
        {
            if (dbTransaction != null) {
                conn = dbTransaction.getConnection();
            } else {
                conn = DataSource.getConnection();
            }

            cstmt = conn.prepareCall("{CALL SP_CREATE_UPDATE_PRODUCT_AATI( ?, ?, ?, ?, ? )}");
            for (int i = 0; i < productListSize; i++)
            {
            	product = productList.get(i);
                int p = 1;
                cstmt.setString (p++, product.getFgPartNumber() );
                cstmt.setString (p++, product.getLevel1PartNumber());
                cstmt.setString (p++, product.getLevel2PartNumber());
                cstmt.setString (p++, product.getLevel3PartNumber());
                cstmt.setString (p++, product.getLevel4PartNumber() );
                cstmt.execute();
            }
            if (dbTransaction == null) {
                if (conn != null) {
                    conn.commit();
                }
            }
            success = true;
        }
        catch (SQLException e) {
            throw new ScalarException("Could not save sales orders", e);
        }
        finally {
            if (dbTransaction == null) {
                if (!success) {
                    try {
                        conn.rollback();
                    } catch (SQLException ignore) {
                    }
                }
            }
            if (cstmt != null) {
                try {
                    cstmt.close();
                    cstmt = null;
                } catch (SQLException ignore) {
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                    conn = null;
                } catch (SQLException ignore) {
                }
            }
        }

	}

	private void log(String message) {
        if (message == null) return;

        if (m_parserLogger != null) {
            m_parserLogger.logDebug(message + AdaptorConstants.LINE_SEPARATOR);
        } else {
            System.out.println(message);
        }
    }

    private File getFileForPorting(String salesPortingFile) throws ScalarException {
        if (salesPortingFile == null || salesPortingFile.trim().length() == 0) {
            log("Sales Porting File Name not specified");
            System.out.println("Sales Porting File Name not specified");
            throw new ScalarException("Sales Porting File Name not specified");
        }
        File portingFile = new File(salesPortingFile);
        if (portingFile.exists() == false && portingFile.isFile() == false) {
            log("Sales Porting File:" + salesPortingFile + " Does not exist");
            throw new ScalarException(salesPortingFile + " does not exist");
        } else {
            log("Sales Porting File:" + salesPortingFile + " Found");
            System.out.println("Sales Porting File:" + salesPortingFile + " Found");
        }
        return portingFile;
    }

    private ArrayList<ProductStructure> parseProductXLSFile(File productFile) throws Exception 
    {
        ArrayList<ProductStructure> productList = new ArrayList<ProductStructure>();
        ProductStructure productStructure = null;
        
        ParserResult result = null;
        Parser parser = ParserFactory.getXLSParser(productFile);

        ((XLSParserImpl) parser).setHeaderRowIdx(6);
        ((XLSParserImpl) parser).setDataStartRowIdx(7);

        result = parser.parse();
        while (result.next()) {
            try {
            	productStructure = new ProductStructure();
            	productStructure.setFgPartNumber( result.getString( "PART NO" ) );
            	productStructure.setLevel1PartNumber( result.getString("LEVEL 1 COMPONENT") );
            	productStructure.setLevel1PartNumber( result.getString("LEVEL 2 COMPONENT") );
            	productStructure.setLevel1PartNumber( result.getString("LEVEL 3 COMPONENT") );
            	productStructure.setLevel1PartNumber( result.getString("LEVEL 4 COMPONENT") );
            	if( productStructure.getFgPartNumber().matches( "AAT.*" ) )
            	{
            		productList.add( productStructure );
            	}
            	
            } catch (Exception e) {
                log("**NOT PORTED** Exception while parsing the line item row:" + (result.getFileRowIdx()+1));
                m_parserLogger.logDebug(e.getMessage(), e);
            }
        }
        System.out.println("Product List " + productList.size());
        return productList;
    }


    public static void main(String[] args) throws Exception {
    	AATIProductLoader salesParser = new AATIProductLoader();
        salesParser.execute();
    }

}
